﻿using NPOI.HSSF.UserModel;
using NPOI.SS.Converter;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using PageAdmin.Utils.ORM;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;

namespace PageAdmin.Utils
{

    /// <summary>
    /// excle文件帮助类
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        ///<param name="filePath">Excle文件路径</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten, string filePath)
        {
            int count = 0;
            ISheet sheet = null;
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            IWorkbook workbook = null;
            if (filePath.IndexOf(".xlsx") > 0) // 2007版本
                workbook = new XSSFWorkbook();
            else if (filePath.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook();
            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return 0;
                }

                if (isColumnWritten == true) //写入DataTable的列名
                {
                    IRow row = sheet.CreateRow(0);
                    row.Height = 25 * 20;
                    for (var j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                        sheet.SetColumnWidth(j, 15 * 256);
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }

                for (var i = 0; i < data.Rows.Count; ++i)
                {
                    IRow row = sheet.CreateRow(count);
                    for (var j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    }
                    ++count;
                }
                workbook.Write(fs); //写入到excel
                return count;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                fs.Close();
                fs = null;
                workbook.Close();
            }
        }


        /// <summary>
        ///  IEnumerable转excle
        /// </summary>
        /// <param name="dataList"></param>
        /// <param name="sheetName"></param>
        /// <param name="filePath"></param>
        /// <param name="fields"></param>
        /// <param name="columnFields">表头，如果不指定，则默认用fields</param>
        /// <returns></returns>
        public static int ListToExcel(IEnumerable<dynamic> dataList, string sheetName, string filePath, IEnumerable<string> fields, IEnumerable<string> columnFields=null)
        {
            int count = 0;
            IWorkbook workbook = null;
            ISheet sheet = null;
            filePath = IOHelper.MapPath(filePath);
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (filePath.IndexOf(".xlsx") > 0) // 2007版本
                workbook = new XSSFWorkbook();
            else if (filePath.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook();

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return 0;
                }
                int j = 0;
                IRow row = sheet.CreateRow(0);
                row.Height = 25 * 20;

                if(columnFields==null)
                {

                    columnFields = fields;
                }
                foreach (var field in columnFields)
                {
                    row.CreateCell(j).SetCellValue(field);
                    sheet.SetColumnWidth(j, 15 * 256);
                    j++;
                    count = 1;
                }
                foreach (dynamic item in dataList)
                {
                    IDictionary<string, dynamic> dic = (IDictionary<string, dynamic>)item;
                    row = sheet.CreateRow(count);
                    j = 0;
                    foreach (var field in fields)
                    {
                        string sss = StringHelper.Format<string>(dic[BasicHelper.FirstCharUpper(field)]);
                        row.CreateCell(j).SetCellValue(sss);
                        j++;
                    }
                    ++count;
                }
                workbook.Write(fs); //写入到excel
                return count;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                fs.Close();
                fs = null;
                workbook.Close();
            }
        }

        /// <summary>
        /// IEnumerable转excle
        /// </summary>
        /// <param name="dataList">泛型方法</param>
        /// <param name="fields"></param>
        /// <param name="sheetName"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static int ListToExcel<T>(IEnumerable<T> dataList, string sheetName, string filePath, IEnumerable<string> fields, IEnumerable<string> columnFields = null) where T:class
        {
            int count = 0;
            IWorkbook workbook = null;
            ISheet sheet = null;
            filePath = IOHelper.MapPath(filePath);
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (filePath.IndexOf(".xlsx") > 0) // 2007版本
                workbook = new XSSFWorkbook();
            else if (filePath.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook();

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return 0;
                }
                int j = 0;
                IRow row = sheet.CreateRow(0);
                row.Height = 25 * 20;
                if (columnFields == null)
                {

                    columnFields = fields;
                }
                foreach (var field in columnFields)
                {
                    row.CreateCell(j).SetCellValue(field);
                    sheet.SetColumnWidth(j, 15 * 256);
                    j++;
                    count = 1;
                }
                Type type = typeof(T); //获取类型
                var properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance); //反射获取属性
                PropertyInfo propertyInfo;
                foreach (T item in dataList)
                {
                    row = sheet.CreateRow(count);
                    j = 0;
                    foreach (var field in fields)
                    {
                        propertyInfo = type.GetProperty(field);
                        if(propertyInfo!=null)
                        {

                        }
                        else
                        {
                            row.CreateCell(j).SetCellValue(propertyInfo.GetValue(item, null).ToString()); //反射获属性对应的值
                        }
                        j++;

                    }

                    ++count;
                }
                workbook.Write(fs); //写入到excel
                return count;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                fs.Close();
                fs = null;
                workbook.Close();
            }
        }

        /// <summary>
        /// IEnumerable转excle
        /// </summary>
        /// <param name="dataList"></param>
        /// <param name="fields"></param>
        /// <param name="sheetName"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static int ListToExcel(IEnumerable<dynamic> dataList, Dictionary<string, string> fields, string sheetName, string filePath)
        {
            int count = 0;
            IWorkbook workbook = null;
            ISheet sheet = null;
            filePath = IOHelper.MapPath(filePath);
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (filePath.IndexOf(".xlsx") > 0) // 2007版本
                workbook = new XSSFWorkbook();
            else if (filePath.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook();

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return 0;
                }
                int j = 0;
                IRow row = sheet.CreateRow(0);
                row.Height = 25 * 20;
                foreach (var field in fields.Keys)
                {
                    row.CreateCell(j).SetCellValue(fields[field]);
                    sheet.SetColumnWidth(j, 15 * 256);
                    j++;
                    count = 1;
                }
                foreach (dynamic item in dataList)
                {
                    IDictionary<string, dynamic> dic = (IDictionary<string, dynamic>)item;
                    row = sheet.CreateRow(count);
                    j = 0;
                    foreach (var field in fields.Keys)
                    {
                        string sss = StringHelper.Format<string>(dic[BasicHelper.FirstCharUpper(field)]);
                        row.CreateCell(j).SetCellValue(sss);
                        j++;
                    }
                    ++count;
                }
                workbook.Write(fs); //写入到excel
                return count;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                fs.Close();
                fs = null;
                workbook.Close();
            }
        }

        /// <summary>
        /// IEnumerable转excle
        /// </summary>
        /// <param name="dataList">泛型方法</param>
        /// <param name="fields"></param>
        /// <param name="sheetName"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static int ListToExcel<T>(IEnumerable<T> dataList, IEnumerable<string> fields, string sheetName, string filePath) where T:class
        {
            int count = 0;
            IWorkbook workbook = null;
            ISheet sheet = null;
            filePath = IOHelper.MapPath(filePath);
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (filePath.IndexOf(".xlsx") > 0) // 2007版本
                workbook = new XSSFWorkbook();
            else if (filePath.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook();

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return 0;
                }
                int j = 0;
                IRow row = sheet.CreateRow(0);
                row.Height = 25 * 20;
                foreach (var field in fields)
                {
                    row.CreateCell(j).SetCellValue(field);
                    sheet.SetColumnWidth(j, 15 * 256);
                    j++;
                    count = 1;
                }
                Type type = typeof(T); //获取类型
                var properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance); //反射获取属性
                foreach (T item in dataList)
                {
                    row = sheet.CreateRow(count);
                    j = 0;
                    foreach (var propertie in properties)
                    {
                        row.CreateCell(j).SetCellValue(StringHelper.Format<string>(item.GetType().GetProperty(propertie.Name).GetValue(item, null))); //反射获属性值
                        j++;
                    }
                    ++count;
                }
                workbook.Write(fs); //写入到excel
                return count;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                fs.Close();
                fs = null;
                workbook.Close();
            }
        }

        /// <summary>
        /// excle文件导入数据库
        /// </summary>
        /// <param name="dataBaseContext"></param>
        /// <param name="table"></param>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static int ExcelToTable(DataBaseContext dataBaseContext,string table,string filePath, string sheetName=null)
        {
            int number = 0;
            dataBaseContext.TransactionBegin();
            IWorkbook workbook = null;
            ISheet sheet = null;
            int startRow = 0;
            filePath = IOHelper.MapPath(filePath);
            FileStream fs = null;
            try
            {
                fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                if (filePath.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (filePath.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                IRow firstRow = sheet.GetRow(0);
                if (sheet != null)
                {
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
                    startRow = sheet.FirstRowNum + 1;

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        DbKeyValueList dbKeyValueList = new DbKeyValueList();
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            ICell cell = firstRow.GetCell(j);
                            string cellName = cell.StringCellValue;
                            if (cellName != null && row.GetCell(j) != null) //tCell(j) != null) //没有数据的单元格都默认是null
                            {
                                var cellValue = row.GetCell(j);
                                if (cellValue != null)
                                {
                                    if (cellValue.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cellValue))
                                    {
                                        dbKeyValueList.Add(cellName, cellValue.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"));
                                    }
                                    else
                                    {
                                        dbKeyValueList.Add(cellName, cellValue.ToString());
                                    }

                                }
                            }
                        }
                        number++;
                        dataBaseContext.Insert(table,dbKeyValueList);
                    }
                }
            }
            catch (Exception ex)
            {
                number = 0;
                dataBaseContext.TransactionRollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                fs.Close();
                fs = null;
                workbook.Close();
            }
            dataBaseContext.TransactionCommit();
            return number;
        }


        /// <summary>
        ///  excle转html文件
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="htmlPath"></param>
        public static void ExcelToHtml(string filePath, string htmlPath)
        {
            ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter();

            // 设置输出参数
            excelToHtmlConverter.OutputColumnHeaders = false;
            excelToHtmlConverter.OutputHiddenColumns = false;
            excelToHtmlConverter.OutputHiddenRows = false;
            excelToHtmlConverter.OutputLeadingSpacesAsNonBreaking = true;
            excelToHtmlConverter.OutputRowNumbers = true;
            excelToHtmlConverter.UseDivsToSpan = true;

            IWorkbook workbook = null;
            filePath = IOHelper.MapPath(filePath);
            try
            {
                FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                if (filePath.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (filePath.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);
                // 处理的Excel文件
                excelToHtmlConverter.ProcessWorkbook(workbook);

                //output the html file
                htmlPath = IOHelper.MapPath(htmlPath);
                excelToHtmlConverter.Document.Save(htmlPath);

            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                workbook.Close();
                workbook = null;
                excelToHtmlConverter = null;
            }
        }

        /// <summary>
        /// 获取excle表集合
        /// </summary>
        /// <param name="outputFile"></param>
        /// <returns></returns>
        public static List<string> GetSheetName(string outputFile)
        {
            List<string> list = new List<string>();
            try
            {
                FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

                HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
                for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
                {
                    list.Add(hssfworkbook.GetSheetName(i));
                }
            }
            catch (Exception e)
            {
                //wl.WriteLogs(exception.ToString());
            }
            return list;
        }

    }
}